

import time
import cPickle
import sqlite3 as lite

from dexen.system.client import db
from dexen.system.common.base import Action



def GetDBSyncher():
    return _db_syncher

#===============================================================================
# 
#===============================================================================
class DBSyncher(object):
    def __init__(self):
        self._conn = None
    
    def close(self):
        self._conn = None
        self.dirty = False

    @property
    def dirty(self):
        from dexen.system.client import ui
        return ui.GetMainWindow().dirty
    
    @dirty.setter
    def dirty(self, value):
        from dexen.system.client import ui
        ui.GetMainWindow().dirty = value

    @property
    def conn(self):
        # Make sure that the connections are cleared in db
        if self._conn is None:
            db.initialize()
            self._conn = db.get_connection()
        return self._conn
    
    def _execute_stmt(self, stmt, params):
        cursor = self.conn.cursor()
        cursor.execute(stmt, params)
        cursor.close()
    
    def _execute_query(self, query, params=None):
        cursor = self.conn.cursor()
        assert isinstance(cursor, lite.Cursor)
        if params != None:
            cursor.execute(query, params)
        else:
            cursor.execute(query)
        res = cursor.fetchall()
        cursor.close()
        return res
    
    def shrink(self):
        self._execute_query("VACUUM")
    
    def get_svr_id(self, svr_info):
        assert isinstance(svr_info, db.ServerInfo)
        query = "SELECT Id FROM Server WHERE IpAddress = ? AND StartTime = ?"
        name = svr_info.get_name()
        start_time = svr_info.get_start_time()
        res = self._execute_query(query, (name, start_time))
        assert len(res) == 1
        svr_id = res[0]["Id"]
        return svr_id
            
    def persist_server_info(self, svr_info):
        assert isinstance(svr_info, db.ServerInfo)
        name = svr_info.get_name()
        start_time = svr_info.get_start_time()
        stmt = "INSERT OR IGNORE INTO Server(IpAddress, StartTime) VALUES(?, ?)"
        self._execute_stmt(stmt, (name, start_time))
        self.conn.commit()
        self.dirty = True

    def remove_server_info(self, svr_info, commit=True):
        svr_id = self.get_svr_id(svr_info)
        stmt = "DELETE FROM Server WHERE Id = ?"
        params = (svr_id,)
        self._execute_stmt(stmt, params)
        if commit:
            self.conn.commit()
        self.dirty = True

    def remove_actions(self, job_info, commit=True):
        svr_id = self.get_svr_id(job_info.get_svr_info())
        stmt = "DELETE FROM Action WHERE JobId = ? AND SvrId = ?"
        params = (job_info.get_id(), svr_id)
        self._execute_stmt(stmt, params)
        if commit:
            self.conn.commit()
        self.dirty = True

    def remove_archives(self, job_info, commit=True):
        svr_id = self.get_svr_id(job_info.get_svr_info())
        stmt = "DELETE FROM Archive WHERE JobId = ? AND SvrId = ?"
        params = (job_info.get_id(), svr_id)
        self._execute_stmt(stmt, params)
        if commit:
            self.conn.commit()
        self.dirty = True

    def remove_job_info(self, job_info, commit=True):
        self.remove_actions(job_info, commit=False)
        self.remove_archives(job_info, commit=False)
        svr_id = self.get_svr_id(job_info.get_svr_info())
        stmt = "DELETE FROM Job WHERE Id = ? AND SvrId = ?"
        params = (job_info.get_id(), svr_id)
        self._execute_stmt(stmt, params)
        if commit:
            self.conn.commit()
        self.dirty = True
    
    def persist_job_info(self, job_info, commit=True):
        assert isinstance(job_info, db.JobInfo)
        svr_info = job_info.get_svr_info()
        assert isinstance(svr_info, db.ServerInfo)
        self.persist_server_info(svr_info)
        svr_id = self.get_svr_id(svr_info)
        job_state = job_info.get_job_state()
        stmt = "INSERT OR REPLACE INTO Job VALUES(?, ?, ?)"
        job_state = cPickle.dumps(job_state)
        params = (job_info.get_id(), job_state, svr_id)
        self._execute_stmt(stmt, params)
        if commit:
            self.conn.commit()
    
    def persist_jobs_info(self, svr_info):
        assert isinstance(svr_info, db.ServerInfo)
        svr_id = self.get_svr_id(svr_info)
        for job_info in svr_info.get_jobs():
            assert isinstance(job_info, db.JobInfo)
            job_state = job_info.get_job_state()
            stmt = "INSERT OR REPLACE INTO Job VALUES(?, ?, ?)"
            job_state = cPickle.dumps(job_state)
            params = (job_info.get_id(), job_state, svr_id)
            self._execute_stmt(stmt, params)
        self.conn.commit()

    def get_server_info_list(self):
        query = "SELECT * FROM Server"
        server_rows = self._execute_query(query)
        
        server_info_list = []
        for row in server_rows:
            server_info = db.ServerInfo(row)
            query = "SELECT JobState FROM Job WHERE SvrId=?"
            job_rows = self._execute_query(query, (row["Id"],))
            for job_row in job_rows:
                job_state = cPickle.loads(str(job_row["JobState"]))
                job_info = db.JobInfo(job_state)
                server_info.add_job(job_info)
            server_info_list.append(server_info)
        return server_info_list

    def persist_actions(self, job_info, actions, commit=True):
        job_id = job_info.get_id()
        svr_info = job_info.get_svr_info()
        svr_id = self.get_svr_id(svr_info)
        print "persisting actions"
        t = time.time()
        for action in actions:
            assert isinstance(action, Action)
            blob = cPickle.dumps(action)
            stmt = "INSERT OR REPLACE INTO Action VALUES(?, ?, ?, ?)"
            params = (action.get_id(), blob, job_id, svr_id)
            self._execute_stmt(stmt, params)
        if commit:
            self.conn.commit()
        self.dirty = True
        print "persisting done in ", time.time() - t, " seconds"

    def get_actions(self, job_info):
        svr_info = job_info.get_svr_info()
        job_id = job_info.get_id()
        svr_id = self.get_svr_id(svr_info)
        query = "SELECT Object FROM Action WHERE SvrId=? AND JobId=?"
        rows = self._execute_query(query, (svr_id, job_id))
        
        actions = []
        for row in rows:
            action = cPickle.loads(str(row["Object"]))
            assert isinstance(action, Action)
            actions.append(action)

        print len(actions), " actions are retrieved from DB"
        return actions
        
    def persist_ind_archive(self, ind_id, filename, content, job_info, commit=True):
        svr_info = job_info.get_svr_info()
        job_id = job_info.get_id()        
        svr_id = self.get_svr_id(svr_info)
        stmt = "INSERT INTO Archive VALUES (?,?,?,?,?)"
        params = (ind_id, filename, buffer(content), job_id, svr_id) 
        self._execute_stmt(stmt, params)
        if commit:
            self.conn.commit()
        self.dirty = True
    
    def commit(self):
        self.conn.commit()
        
    def get_ind_archive(self, ind_id, name, job_info):
        svr_info = job_info.get_svr_info()
        job_id = job_info.get_id()        
        svr_id = self.get_svr_id(svr_info)
        query = """SELECT Content FROM Archive, Job WHERE IndId = ? AND 
                Archive.Name = ? AND Archive.JobId = ? AND Job.Id = ?
                AND Job.SvrId = ? """
        params = (ind_id, name, job_id, job_id, svr_id)
        rows = self._execute_query(query, params)
        if len(rows) == 0:
            return None
        assert len(rows) == 1 
        return str(rows[0]['Content'])
    
    def get_ind_all_archives(self, ind_id, job_info):
        svr_info = job_info.get_svr_info()
        job_id = job_info.get_id()        
        svr_id = self.get_svr_id(svr_info)
        query = """SELECT Archive.Name, Content FROM Archive, Job WHERE 
                IndId = ? AND Archive.JobId = ? AND Job.Id = ?
                AND Job.SvrId = ?"""
        params = (ind_id, job_id, job_id, svr_id)
        rows = self._execute_query(query, params)
        res = {}
        for row in rows:
            res[row['Name']] = str(row['Content'])
        return res
    
    def get_ind_archive_names(self, ind_id, job_info):
        svr_info = job_info.get_svr_info()
        job_id = job_info.get_id()        
        svr_id = self.get_svr_id(svr_info)
        query = """SELECT Archive.Name FROM Archive, Job WHERE IndId = ? 
                AND Archive.JobId = ? AND Job.Id = ? AND Job.SvrId = ?"""
        params = (ind_id, job_id, job_id, svr_id)
        rows = self._execute_query(query, params)
        res = [x['Name'] for x in rows]
        return res

    def get_all_archive_names(self, job_info):
        svr_info = job_info.get_svr_info()
        job_id = job_info.get_id()        
        svr_id = self.get_svr_id(svr_info)
        query = """SELECT Archive.IndId, Archive.Name FROM Archive, Job WHERE 
                Archive.JobId = ? AND Job.Id = ? AND Job.SvrId = ?"""
        params = (job_id, job_id, svr_id)
        rows = self._execute_query(query, params)
        
        res = {}
        for row in rows:
            ind_id = row['IndId']
            if not res.has_key(ind_id):
                res[ind_id] = set()
            res[ind_id].add(row['Name'])
        return res
    
_db_syncher = DBSyncher() 
    